import matplotlib.pyplot as plt
import numpy as np
from openpyxl import load_workbook
import matplotlib.ticker as ticker
from pptx import Presentation as Presentation
from pptx.util import Pt, Cm
from pptx.enum.shapes import MSO_SHAPE_TYPE
from pptx.dml.color import RGBColor
def show_pic(datas, info):
    dim = info[0]
    normal = info[1]
    usl = info[2]
    lsl = info[3]
    # 创建图形和轴，设置图片大小为800x600像素
    fig, ax = plt.subplots(figsize=(15, 9))
    bps = []
    pos = []
    xtick = []
    colors = plt.cm.tab10.colors 

    num_datasets = len(datas)
    if num_datasets > 0:
        start = 0.5
        end = 15.5  # 图表的宽度减去一些边距
        step = (end - start) / (num_datasets + 1)

        for i, data in enumerate(datas):
            label = data[0]
            values = data[1]
            # 计算每个数据集的位置
            position = start + (i + 1) * step
            pos.append(position)
            xtick.append(label)
            # 绘制box plot
            bp = ax.boxplot([values], positions=[position], widths=0.3, patch_artist=True, boxprops=dict(facecolor='none', color=colors[i%10]))
            bps.append(bp)
            x = np.random.normal(position, 0.05, len(values))  # 对应的x坐标，围绕position分布
            y = values
            scatter = ax.scatter(x, y, color=colors[i%10], label=label)

    ax.set_xlabel("Data")
    ax.tick_params(axis='y', labelcolor='black')

    # 设置box plot的背景色为透明
    for bp in bps:
        for box in bp['boxes']:
            box.set(facecolor='none')  # 设置背景色为透明

    # 添加规格线
    if normal != "NA":
        ax.axhline(y=normal, color='black', linestyle='--')
    if lsl != "NA":
        ax.axhline(y=lsl, color='red', linestyle='--')
    if usl != "NA":
        ax.axhline(y=usl, color='red', linestyle='--')

    # 设置标题
    plt.title(dim)

    # 设置Y轴刻度格式为两位小数
    ax.yaxis.set_major_formatter(ticker.FormatStrFormatter('%.2f'))

    # 设置x轴刻度位置和标签
    ax.set_xticks(pos)
    ax.set_xticklabels(xtick)

    # 调整x轴限制以确保文本标签显示在图表内
    ax.set_xlim(start, end)
    for spine in ax.spines.values():
        spine.set_visible(True)
        spine.set_color('black')
        spine.set_linewidth(1)
    # 调整子图参数以去除边框
    plt.subplots_adjust(left=0.05, right=0.95, top=0.95, bottom=0.05)

    # 保存为图片
    plt.savefig('d:/{}.png'.format(dim), dpi=300, bbox_inches='tight', pad_inches=0)  # 设置dpi以提高图片质量
TEXTBOX_POSITION = (Cm(1), Cm(0), Cm(15), Cm(2))
LINE_POSITION = (Cm(1), Cm(2), Cm(31.867), Cm(0))
IMAGE_POSITION = (Cm(1), Cm(2.5))
IMAGE_SIZE = (Cm(16), Cm(16))  # 假设目标尺寸，实际根据图片比例调整
print("开始生成加载D盘get_pic_cav.xlsx")
print("加载中...")
wb = load_workbook('d:/get_pic_cav.xlsx')
ws = wb.active
paths = []
print("加载完成")
datas = []
cav=[]
for i in range(5, ws.max_row + 1):
    if ws.cell(i, 2).value not in cav:
        cav.append(ws.cell(i, 2).value)
# 先获取Info信息
infos = []
for i in range(3, ws.max_column + 1): 
    dim = ws.cell(1, i).value.replace(" /", "_")
    # 判断dim内是否有-符号
    if "-" in dim:  # 多乘
        tdim = dim.split("-")[0]
        infos.append([dim, "M", tdim])
    else:  # 单乘
        infos.append([dim, "S"])
# 先处理单乘的数据
for i in range(3, ws.max_column + 1): 
    datas = []
    dim = ws.cell(1, i).value.replace(" /", "_")
    for s in infos:
        if s[0] == dim and s[1] == "S":
            normal = ws.cell(2, i).value
            usl = ws.cell(3, i).value
            lsl = ws.cell(4, i).value
            info = [dim, normal, usl, lsl]
            for c in cav:
                data = []
                for j in range(5, ws.max_row + 1):
                    if ws.cell(j, 2).value == c:
                        data.append(ws.cell(j, i).value)
                datas.append([c, data])
            show_pic(datas, info)
            pic_path = 'd:/{}.png'.format(info[0])
            paths.append([info[0], pic_path])
            print("生成图片：{}".format(info[0]))
m_list = []
for info in infos:
    if info[1] == "M":
        if info[2] not in m_list:
            m_list.append(info[2])
for m in m_list:
    datas = []
    for i in range(3, ws.max_column + 1): 
        dim = ws.cell(1, i).value.replace(" /", "_")
        if "-" in dim:  # 多乘
            tdim = dim.split("-")[0]
            nums= dim.split("-")[1]
            if tdim == m:
                normal = ws.cell(2, i).value
                usl = ws.cell(3, i).value
                lsl = ws.cell(4, i).value
                info = [m, normal, usl, lsl]
                for c in cav:
                    data = []
                    for j in range(5, ws.max_row + 1):
                        if ws.cell(j, 2).value == c:
                            data.append(ws.cell(j, i).value)
                    datas.append([c+"_"+nums, data])
    #对datas进行排序
    datas.sort(key=lambda x: x[0])
    show_pic(datas, info)
    pic_path = 'd:/{}.png'.format(info[0])
    paths.append([info[0], pic_path])
    print("生成图片：{}".format(info[0]))
presentation = Presentation()
presentation.slide_height = 6858000
presentation.slide_width = 12192000
blank_layout = presentation.slide_layouts[6]
print("开始生成PPT")
for path in paths:
    img_path = path[1]
    slide = presentation.slides.add_slide(blank_layout)
    textbox = slide.shapes.add_textbox(*TEXTBOX_POSITION)
    tf = textbox.text_frame
    tf.text = path[0]
    font = tf.paragraphs[0].font
    font.name = "SF Hello Light"
    font.size = Pt(40)
    line = slide.shapes.add_shape(MSO_SHAPE_TYPE.LINE, *LINE_POSITION)
    line.line.color.rgb = RGBColor(0, 0, 0)
    line.line.width = Pt(2)
    aspect_ratio = 1.6
    slide.shapes.add_picture(img_path, *IMAGE_POSITION, width=IMAGE_SIZE[0]*aspect_ratio, height=IMAGE_SIZE[1])
    print("生成PPT：{}".format(path[0]))
presentation.save("d:/new_pic.pptx")
print("PPT生成完成")